#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Date : 2024/7/10
# @Author : shiyou pan

from pathlib import Path

import pandas as pd
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment


def write_to_excel(excel_path: str,
                   indata: pd.DataFrame,
                   info: str | None = None,
                   shtname: str = 'Sheet1',
                   index_format: str = 'yyyy-mm-dd',
                   data_format: str = '#,##0.00',
                   ) -> None:
    if not Path(excel_path).is_file():
        pd.DataFrame().to_excel(excel_path, sheet_name=shtname)

    hfont = Font(size=9, name='微软雅黑', bold=True, color='FFFFFF')
    bfont = Font(size=9, name='微软雅黑', bold=False, color='000000')
    hpattern = PatternFill(fill_type='solid', start_color='555555', end_color='555555')
    bpattern1 = PatternFill(fill_type='solid', start_color='FFFFFF', end_color='FFFFFF')
    bpattern2 = PatternFill(fill_type='solid', start_color='EEEEEE', end_color='EEEEEE')
    hborder = Border(left=Side(style='thin', color='FFFFFF'), right=Side(style='thin', color='FFFFFF'),
                     top=Side(style='thin', color='FFFFFF'), bottom=Side(style='thin', color='FFFFFF'))
    bborder = Border(left=Side(style='thin', color='000000'), right=Side(style='thin', color='000000'),
                     top=Side(style='thin', color='000000'), bottom=Side(style='thin', color='000000'))
    halign = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ialign = Alignment(horizontal='left', vertical='center', wrap_text=False)
    dalign = Alignment(horizontal='right', vertical='center', wrap_text=False)
    iformat = index_format
    dformat = data_format

    with pd.ExcelWriter(excel_path, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
        rw = 3 if info is not None else 1
        indata.to_excel(writer, sheet_name=shtname, index=True, header=True, startrow=rw - 1)
        worksheet = writer.sheets[shtname]
        worksheet.freeze_panes = worksheet['B4'] if rw == 3 else worksheet['B2']
        worksheet.sheet_view.showGridLines = False

        # 设置数据区域样式
        i = 0
        for row in worksheet.iter_rows(min_row=rw,
                                       max_row=indata.shape[0] + rw,
                                       min_col=1,
                                       max_col=indata.shape[1] + 1):
            i += 1
            for cell in row:
                if i == 1:
                    cell.font = hfont
                    cell.fill = hpattern
                    cell.alignment = halign
                    cell.border = hborder
                else:
                    cell.font = bfont
                    cell.fill = bpattern1 if i % 2 == 0 else bpattern2
                    cell.alignment = ialign if cell.column == 1 else dalign
                    cell.number_format = iformat if cell.column == 1 else dformat
                    cell.border = bborder

        # 设置信息栏样式
        if info is not None:
            worksheet.cell(row=1, column=1, value=info)
